import logging
import math
import time
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_good_class_router = APIRouter(prefix="/material/goodClass", tags=["危化品物料分类管理"],
                                   dependencies=[Depends(oauth2_scheme)])


class HazardousMaterialClassSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fclassstname: Optional[str] = Field("", description="分类名称（支持前缀匹配）")
    fparentno: Optional[str] = Field("", description="父级分类编号")


@data_good_class_router.get("/list", summary="危化品物料分类接口")
def list_good_class(search: HazardousMaterialClassSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select fsite_no,fclassstno,fclassstname,fparentno,fclassstyle,flevel,feffday,fupddate,foperator"
    sql = sql + " from lims.i_ts_good_class where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fclassstname != "":
        sql = sql + " and fclassstname like concat('%', :fclassstname ,'%')"
        param["fclassstname"] = search.fclassstname
    if search.fparentno != "":
        sql = sql + " and fparentno = :fparentno "
        param["fparentno"] = search.fparentno
    if search.order == "desc":
        sql = sql + " order by  fclassstno desc "
    else:
        sql = sql + " order by  fclassstno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fsite_no,fclassstno,fclassstname,fparentno,fclassstyle,flevel,feffday,fupddate,foperator"
    sql = sql + " from lims.i_ts_good_class where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fclassstname != "":
        sql = sql + " and fclassstname like concat('%', :fclassstname ,'%')"
        param["fclassstname"] = search.fclassstname
    if search.fparentno != "":
        sql = sql + " and fparentno = :fparentno "
        param["fparentno"] = search.fparentno
    if search.order == "desc":
        sql = sql + " order by  fclassstno desc "
    else:
        sql = sql + " order by  fclassstno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteno'] = data[key][0]
        d1['fclassstno'] = data[key][1]
        d1['fclassstname'] = data[key][2]
        d1['fparentno'] = data[key][3]
        d1['fclassstyle'] = data[key][4]
        d1['flevel'] = data[key][5]
        d1['feffday'] = data[key][6]
        d1['fupddate'] = data[key][7]
        d1['foperator'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class MaterialClassCreate(BaseModel):
    """物料分类新增请求参数"""
    fclassstno: str = Field(..., description="分类编号（唯一标识）")
    fclassstname: str = Field(..., description="分类名称")
    fsiteNo: str = Field(..., description="站点编号")
    fparentno: Optional[str] = Field("", description="父级分类编号")


@data_good_class_router.post("/create", summary="物料分类新增接口")
def create_good_class(create: MaterialClassCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fclassstno = create.fclassstno
        fclassstname = create.fclassstname
        fsiteNo = create.fsiteNo
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fclassstno from lims.i_ts_good_class where fclassstno= :fclassstno  and fsite_No= :fsiteNo "
            data = db.execute(text(sql), {
                "fclassstno": fclassstno,
                "fsiteNo": fsiteNo,
            }).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="耗材分类编号已存在")
            else:
                sql = "insert into lims.i_ts_good_class(fsite_no,fclassstno,fclassstname,fparentno,fclassstyle,flevel,feffday,fupddate,foperator)"
                sql = sql + " values( :fsiteNo , :fclassstno , :fclassstname , :fparentno ,0,1,0,now(), :userid );"
                db.execute(text(sql), {
                    "fsiteNo": fsiteNo,
                    "fclassstno": fclassstno,
                    "fclassstname": fclassstname,
                    "fparentno": create.fparentno,
                    "userid": user_id,
                })
                # 返回
                current_datetime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                d1 = {}
                d1['fsiteNo'] = fsiteNo
                d1['fclassstno'] = fclassstno
                d1['fclassstname'] = fclassstname
                d1['fparentno'] = create.fparentno
                d1['fclassstyle'] = 1
                d1['flevel'] = 1
                d1['feffday'] = 0
                d1['fupddate'] = current_datetime
                d1['foperator'] = user_id
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_good_class_router.post("/update", summary="危化品物料分类修改接口")
def update_good_class(update: MaterialClassCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fsiteNo = update.fsiteNo
        fclassstno = update.fclassstno
        fclassstname = update.fclassstname
        fparentno = update.fparentno
        with db.begin():
            sql = "update lims.i_ts_good_class"
            sql = sql + " set fclassstname= :fclassstname "
            sql = sql + " ,fupddate=now(),foperator= :userid "
            if fparentno is not None:
                sql = sql + ",fparentno= :fparentno "
            sql = sql + " where fclassstno= :fclassstno and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fclassstname": fclassstname,
                "userid": user_id,
                "fparentno": fparentno,
                "fclassstno": fclassstno,
                "fsiteNo": fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_good_class_router.post("/delete/{fclassstno}", summary="物料分类删除接口")
def delete_good_class(fclassstno: str = Path(description="分类编号"), db: Session = Depends(get_db),
                      token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select sum(f1) "
            sql = sql + " from ( select count(1) f1 from lims.i_ts_good_class where fparentno= :fclassstno "
            sql = sql + " union"
            sql = sql + " select count(1) f1 from lims.i_ts_good_info where fclassstno= :fclassstno ) a "
            data = db.execute(text(sql), {
                "fclassstno": fclassstno,
            }).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from lims.i_ts_good_class where fclassstno= :fclassstno "
                db.execute(text(sql), {
                    "fclassstno": fclassstno,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
